﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BE;
using System.Data.SqlClient;

namespace DAL
{
	public class RoomDA
	{
		//Define Parameters to use in objectCommand objects
		private const string ParamId = "@id";
		private const string ParamName = "@name";
		private const string ParamLID = "@location";

		//define name of Database values
		private const string Table = "Rooms";
		private const string ColId = "ID";
		private const string ColName = "Name";
		private const string ColLID = "Location";
		private const string space = ", ";

		private SqlConnection con;

		public RoomDA()
		{
			SqlCommands cmd = new SqlCommands();
			con = cmd.GetConnection();
		}

		private Room extractRoom( SqlDataReader reader )
		{
			int Lid;
			if(reader.IsDBNull( reader.GetOrdinal( ColLID )))
				Lid=0;
			else
				Lid=reader.GetInt32( reader.GetOrdinal( ColLID ) );
			return new Room( reader.GetInt32( reader.GetOrdinal( ColId ) ),
				reader.GetString( reader.GetOrdinal( ColName ) ),
				Lid );

		}

		private void setCommand( SqlCommand cmd, Room room )
		{
			cmd.Parameters.AddWithValue( ParamName, room.Name );
			cmd.Parameters.AddWithValue( ParamLID, room.LocationID );
		}

		public int AddRoom( Room room )
		{
			string insertString = "INSERT INTO " + Table + "(" + ColName + space + ColLID + ") VALUES(" + ParamName + space + ParamLID + ")";
			SqlCommand cmd = new SqlCommand( insertString, con );
			setCommand( cmd, room );
			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public Room GetRoom( int id )
		{
			string sqlString = "SELECT * FROM " + Table + " WHERE " + ColId + " = " + id;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				reader.Read();
				Room r = extractRoom( reader );
				reader.Close();
				return r;
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public List<Room> GetAllRooms()
		{
			string sqlString = "SELECT * FROM " + Table;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				List<Room> list = new List<Room>();
				while ( reader.Read() )
					list.Add( extractRoom( reader ) );
				reader.Close();
				return list;
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				con.Close();
			}
		}

		public int EditRoom( Room room )
		{
			string sqlString = "UPDATE "+Table+" set ";
			
			sqlString += ColName + "=" + ParamName+space;
			sqlString += ColLID + "=" + ParamLID;
			sqlString += " Where " + ColId +"="+ room.ID;
			
			SqlCommand cmd = new SqlCommand( sqlString, con );
			setCommand( cmd, room );
			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				con.Close();
			}
		}

		public int DeleteRoom( int id )
		{
			string sqlString = "DELETE FROM " + Table + " WHERE "+ColId+"="+id;

			
			SqlCommand cmd = new SqlCommand( sqlString, con );
			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

        public List<Room> GetLocationLessRooms()
        {
            string sqlString = "SELECT * FROM " + Table + " WHERE " + ColLID + "= 0";
            SqlCommand cmd = new SqlCommand(sqlString, con);
            SqlDataReader reader;
            try
            {
                con.Open();
                reader = cmd.ExecuteReader();
                List<Room> list = new List<Room>();
                while (reader.Read())
                    list.Add(extractRoom(reader));
                reader.Close();
                return list;
            }
            catch
            {
                throw new EasyException("There was something wrong with the Database Connection");
            }
            finally
            {
                con.Close();
            }
        }

		public List<Room> GetRoomsByLocation(int location)
		{
			string sqlString = "SELECT * FROM " + Table + " WHERE " + ColLID + "= "+location;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				List<Room> list = new List<Room>();
				while ( reader.Read() )
					list.Add( extractRoom( reader ) );
				reader.Close();
				return list;
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}
    }
}
